#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3
import csv
import re

def csv2sql(csvFile):
    '''
    insert into teachers table data in csvFile (.csv)
    the fields in file must be:
    id, nome, grau, curso, categoria, regime, ano,
    tipo_estabelecimento, nome_estabelecimento
    WARNING: '\' char is replaced by [|], it should be reconverted on reading
    '''
    connection = sqlite3.connect("teachers.db")
    c = connection.cursor()
    # to prevent records to be duplicated id code is the primary key
    c.execute('''CREATE TABLE IF NOT EXISTS teachers
                (id INTEGER, nome TEXT, grau TEXT, curso TEXT,
                categoria TEXT, regime TEXT, ano TEXT, tipo_estabelecimento TEXT,
                nome_estabelecimento TEXT)''')

    spamReader = csv.reader(open(csvFile, 'rb'), delimiter=',')
    spamReader.next()
    for row in spamReader:
        for i in range(0,8):
            row[i] = row[i].replace("\\", r'\\\\');
            row[i] = row[i].replace("\"", r'\\x22');
        stmt = '''INSERT OR REPLACE INTO teachers (id, nome, grau, curso,
                  categoria, regime, ano, tipo_estabelecimento,
                  nome_estabelecimento) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}",
                  "{5}", "{6}", "{7}", "{8}")'''.\
                  format(row[0], row[1], row[2], row[3], row[4],
                         row[5], row[6], row[7], row[8])

        try:
            c.execute(stmt)
            connection.commit()
        except:
            print("invalid data for insertion: {0}\n", str(row))

        
   
    pass

    print("FILE DONE\n")
    connection.close()
pass

print "\nSTART\n"

def autoInsertion():
    csv2sql('rebcsv/reb0.csv')
    #csv2sql('rebcsv/reb1.csv')
    #csv2sql('rebcsv/reb2.csv')
    #csv2sql('rebcsv/reb3.csv')
    #csv2sql('rebcsv/reb4.csv')
    #csv2sql('rebcsv/reb5.csv')
    #csv2sql('rebcsv/reb6.csv')
    #csv2sql('rebcsv/reb7.csv')
    #csv2sql('rebcsv/reb8.csv')
    #csv2sql('rebcsv/reb9.csv')
    #csv2sql('rebcsv/reb10.csv')
    print "\nDONE\n"
pass

autoInsertion()

